SQL SERVER – 2005 – Find Tables With Foreign Key Constraint in Database

A foreign key inventory needs both the constraint and its column pairings. I include schema names to identify each table clearly.

After writing about tables without a clustered index or primary key, I wanted the corresponding foreign-key inventory. The original script already used the right two catalog views, despite its introductory label mistakenly saying primary key.

SELECT fk.name AS foreign_key,
       OBJECT_SCHEMA_NAME(fk.parent_object_id) AS child_schema,
       OBJECT_NAME(fk.parent_object_id) AS child_table,
       COL_NAME(fkc.parent_object_id, fkc.parent_column_id) AS child_column,
       OBJECT_SCHEMA_NAME(fk.referenced_object_id) AS parent_schema,
       OBJECT_NAME(fk.referenced_object_id) AS parent_table,
       COL_NAME(fkc.referenced_object_id, fkc.referenced_column_id) AS parent_column,
       fkc.constraint_column_id, fk.is_disabled, fk.is_not_trusted
FROM sys.foreign_keys AS fk
JOIN sys.foreign_key_columns AS fkc ON fkc.constraint_object_id = fk.object_id
ORDER BY child_schema, child_table, foreign_key, fkc.constraint_column_id;

Run this in the database you intend to inspect. Schema names distinguish tables with the same name. constraint_column_id preserves the pairing order in a composite foreign key.

Check enforcement and trust

is_disabled and is_not_trusted help distinguish a declared relationship from a trusted, enabled constraint. Metadata visibility depends on permissions; an empty result is not proof that no relationships exist anywhere.

Foreign keys enforce declared relationships. I choose them from the data model. They don’t automatically create indexes on referencing columns. Those access paths need separate investigation.

Related reading

• Find Table without Clustered Index – Find Table with no Primary Key
• Find Tables With Primary Key Constraint in Database

A foreign key is not an automatic access path, it is an enforced relationship when enabled.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.